﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ProcCodeCreator
{
    public partial class frmMain : Form
    {
        public string ConnectionString = "Data Source=10.100.23.222;Initial Catalog=LMSForCDB2;User ID=sa;Password=zhangzhen5847;";

        public frmMain()
        {
            InitializeComponent();
        }

        private DataTable GetSource()
        {
            string sqlstr = "select name from sysobjects where xtype = 'P' order by name";
            return SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, sqlstr, null).Tables[0];
        }

        private void Init()
        {
            DataTable dtSource = GetSource();

            foreach (DataRow dr in dtSource.Rows)
            {
                this.lt_Source.Items.Add(dr[0].ToString());
            }
        }

        private void frmMain_Load(object sender, EventArgs e)
        {
            Init();
        }

        private void btn_add_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.ListBox.SelectedObjectCollection items = this.lt_Source.SelectedItems;
            foreach (var item in items)
            {
                this.lt_Target.Items.Add(item);
            }

            for (int j = 0; j < lt_Target.Items.Count; j++)
            {
                for (int i = 0; i < lt_Source.Items.Count; i++)
                {
                    if (lt_Target.Items[j].ToString() == lt_Source.Items[i].ToString())
                    {
                        lt_Source.Items.RemoveAt(i);
                        break;
                    }
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.ListBox.ObjectCollection items = this.lt_Source.Items;
            foreach (var item in items)
            {
                this.lt_Target.Items.Add(item);
            }

            for (int j = 0; j < lt_Target.Items.Count; j++)
            {
                for (int i = 0; i < lt_Source.Items.Count; i++)
                {
                    if (lt_Target.Items[j].ToString() == lt_Source.Items[i].ToString())
                    {
                        lt_Source.Items.RemoveAt(i);
                        break;
                    }
                }
            }
        }

        private void btn_Remove_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.ListBox.SelectedObjectCollection items = this.lt_Target.SelectedItems;
            foreach (var item in items)
            {
                this.lt_Source.Items.Add(item);
            }

            for (int j = 0; j < lt_Source.Items.Count; j++)
            {
                for (int i = 0; i < lt_Target.Items.Count; i++)
                {
                    if (lt_Source.Items[j].ToString() == lt_Target.Items[i].ToString())
                    {
                        lt_Target.Items.RemoveAt(i);
                        break;
                    }
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (this.folderBrowserDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                Writer.SetPath(this.folderBrowserDialog1.SelectedPath);

                for (int i = 0; i < lt_Target.Items.Count; i++)
                {
                    Create(this.lt_Target.Items[i].ToString());
                }

                MessageBox.Show("completed");
            }
        }

        private void Create(string PName)
        {
            DataTable dt = GetColumsTable(PName);

            List<Param> pList = GetPList(dt);

            List<RetC> llist = GetRetList(pList, PName);

            if (llist == null)
            {
                string ProceDAL = Common.GetProce(pList, PName);
                Writer.SaveDAL(@"LMS.txt", ProceDAL);
            }
            else
            {
                string ProceModel = Common.GetProceModel(llist, PName);
                Writer.SaveModel(PName + "_Result" + @".cs", ProceModel);

                string ProceDAL = Common.GetProceForModel(pList, PName);
                Writer.SaveDAL(@"LMS.txt", ProceDAL);
            }
        }

        private List<RetC> GetRetList(List<Param> pList, string pName)
        {
            StringBuilder sql = new StringBuilder();



            sql.Append("exec " + pName + " ");
            foreach (Param p in pList)
            {
                if (
                    p.DBType == "bigint" ||
                    p.DBType == "bit" ||
                    p.DBType == "decimal" ||
                    p.DBType == "float" ||
                    p.DBType == "int" ||
                    p.DBType == "smallint" )
                {
                    sql.Append(" 0, ");
                }
                else if
                (
                    p.DBType == "tinyint" ||
                    p.DBType == "char" ||
                    p.DBType == "nchar" ||
                    p.DBType == "ntext" ||
                    p.DBType == "nvarchar" ||
                    p.DBType == "text" ||
                    p.DBType == "varchar" ||
                    p.DBType == "date" ||
                    p.DBType == "datetime"
                    )
                {
                    sql.Append(" '', ");
                }

            }
            string sqlstr = sql.ToString();
            sqlstr = sqlstr.Substring(0, sqlstr.Length - 2);

            DataSet ds;
            try
            {
                ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, sqlstr);

            }
            catch
            {
                return null;
            }
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            List<RetC> retList = new List<RetC>();
            foreach (DataColumn dc in ds.Tables[0].Columns)
            {
                RetC c = new RetC();
                c.Name = dc.ColumnName;
                c.Type = dc.DataType.ToString();
                retList.Add(c);
            }

            return retList;
        }

        private List<Param> GetPList(DataTable dt)
        {
            List<Param> pList = new List<Param>();
            foreach (DataRow dr in dt.Rows)
            {
                Param p = new Param();
                p.Name = dr[0].ToString();
                p.Type = dr[2].ToString();
                p.OutType = dr[1].ToString();
                p.Length = int.Parse(dr[3].ToString());
                pList.Add(p);
            }
            return pList;
        }

        private DataTable GetColumsTable(string PName)
        {
            string sqlstr = @"select c.name,
                    case c.isoutparam
                    when 1 then 'out'
                    else ''
                    end as outtype
                    ,t.name,c.length from syscolumns c
                    left join  systypes t on c.xtype = t.xtype
                    where id =
                    (select id from sysobjects where xtype = 'P' and name = '" + PName + "') order by c.name";

            return SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, sqlstr, null).Tables[0];
        }
    }
}
